/**********************************************************************/
/* InstallCommon.SQL  - SCHEMA REVAMP VERSION                         */
/*                                                                    */
/* Installs the tables, triggers and stored procedures necessary for  */
/* supporting some features of ASP.Net                                */
/*                                                                    */
/* Copyright Microsoft, Inc. 2003                                     */
/* All Rights Reserved.                                               */
/*                                                                    */
/**********************************************************************/

PRINT '---------------------------------------'
PRINT 'Starting execution of InstallCommon.SQL'
PRINT '---------------------------------------'
GO

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULL_DFLT_ON ON
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the db schema if needed
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'aspnet'))
BEGIN
	DECLARE @command nvarchar(500) = 'CREATE SCHEMA aspnet'
	EXEC(@command)
END
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = N'Setup_RemoveAllRoleMembers')))
DROP PROCEDURE [aspnet].Setup_RemoveAllRoleMembers
GO

CREATE PROCEDURE [aspnet].Setup_RemoveAllRoleMembers
	@name   sysname
AS
BEGIN
	CREATE TABLE #aspnet_RoleMembers
	(
		Group_name      sysname,
		Group_id        smallint,
		Users_in_group  sysname,
		User_id         smallint
	)

	INSERT INTO #aspnet_RoleMembers
	EXEC sp_helpuser @name

	DECLARE @user_id smallint
	DECLARE @cmd nvarchar(500)
	DECLARE c1 cursor FORWARD_ONLY FOR
		SELECT User_id FROM #aspnet_RoleMembers

	OPEN c1

	FETCH c1 INTO @user_id
	WHILE (@@fetch_status = 0)
	BEGIN
		SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
		EXEC (@cmd)
		FETCH c1 INTO @user_id
	END

	CLOSE c1
	DEALLOCATE c1
END
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the aspnet.Applications table.

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'Applications')))
BEGIN
  PRINT 'Creating the aspnet.Applications table...'
  CREATE TABLE [aspnet].Applications (
	ApplicationName         nvarchar(256)               NOT NULL UNIQUE,
	LoweredApplicationName  nvarchar(256)               NOT NULL UNIQUE,
	ApplicationId           uniqueidentifier            PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
	Description             nvarchar(256)       )
  CREATE CLUSTERED INDEX aspnet_Applications_Index ON [aspnet].Applications(LoweredApplicationName)
END
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the aspnet.Users table
IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'Users')))
BEGIN
  PRINT 'Creating the aspnet.Users table...'
  CREATE TABLE aspnet.Users (
	ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES [aspnet].Applications(ApplicationId),
	UserId           uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
	UserName         nvarchar(256)       NOT NULL,
	LoweredUserName  nvarchar(256)	     NOT NULL,
	MobileAlias      nvarchar(16)        DEFAULT NULL,
	IsAnonymous      bit                 NOT NULL DEFAULT 0,
	LastActivityDate DATETIME            NOT NULL)

   CREATE UNIQUE CLUSTERED INDEX aspnet_Users_Index ON [aspnet].Users(ApplicationId, LoweredUserName)
   CREATE NONCLUSTERED INDEX aspnet_Users_Index2 ON [aspnet].Users(ApplicationId, LastActivityDate)
END
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the aspnet.SchemaVersions table
IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'SchemaVersions')))
BEGIN
  PRINT 'Creating the aspnet.SchemaVersions table...'
  CREATE TABLE [aspnet].SchemaVersions (
    Feature                  nvarchar(128)  NOT NULL PRIMARY KEY CLUSTERED( Feature, CompatibleSchemaVersion ),
    CompatibleSchemaVersion  nvarchar(128)	NOT NULL,
    IsCurrentVersion         bit            NOT NULL )
END
GO

/*************************************************************/
/*************************************************************/
------------- Create Stored Procedures
/*************************************************************/
/*************************************************************/
-- RegisterSchemaVersion SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'RegisterSchemaVersion')))
DROP PROCEDURE [aspnet].RegisterSchemaVersion
GO

CREATE PROCEDURE [aspnet].RegisterSchemaVersion
	@Feature                   nvarchar(128),
	@CompatibleSchemaVersion   nvarchar(128),
	@IsCurrentVersion          bit,
	@RemoveIncompatibleSchema  bit
AS
BEGIN
	IF( @RemoveIncompatibleSchema = 1 )
	BEGIN
		DELETE FROM aspnet.SchemaVersions WHERE Feature = LOWER( @Feature )
	END
	ELSE
	BEGIN
		IF( @IsCurrentVersion = 1 )
		BEGIN
			UPDATE aspnet.SchemaVersions
			SET IsCurrentVersion = 0
			WHERE Feature = LOWER( @Feature )
		END
	END

	INSERT  aspnet.SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
	VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END
GO

DECLARE @command nvarchar(4000)

SET @command = 'GRANT EXECUTE ON [aspnet].RegisterSchemaVersion TO ' + QUOTENAME(user)
EXEC (@command)
GO

-- Create common schema version
EXEC [aspnet].RegisterSchemaVersion N'Common', N'1', 1, 1
GO

/*************************************************************/
/*************************************************************/
-- CheckSchemaVersion SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'CheckSchemaVersion')))
DROP PROCEDURE [aspnet].CheckSchemaVersion
GO

CREATE PROCEDURE [aspnet].CheckSchemaVersion
	@Feature                   nvarchar(128),
	@CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
	IF (EXISTS( SELECT  *
				FROM    aspnet.SchemaVersions
				WHERE   Feature = LOWER( @Feature ) AND
						CompatibleSchemaVersion = @CompatibleSchemaVersion ))
		RETURN 0

	RETURN 1
END
GO

/*************************************************************/
/*************************************************************/
-- CreateApplication SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'Applications_CreateApplication')))
DROP PROCEDURE [aspnet].Applications_CreateApplication
GO

CREATE PROCEDURE [aspnet].Applications_CreateApplication
	@ApplicationName      nvarchar(256),
	@ApplicationId        uniqueidentifier OUTPUT
AS
BEGIN
	SELECT  @ApplicationId = ApplicationId FROM aspnet.Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

	IF(@ApplicationId IS NULL)
	BEGIN
		DECLARE @TranStarted   bit
		SET @TranStarted = 0

		IF( @@TRANCOUNT = 0 )
		BEGIN
			BEGIN TRANSACTION
			SET @TranStarted = 1
		END
		ELSE
			SET @TranStarted = 0

		SELECT  @ApplicationId = ApplicationId
		FROM aspnet.Applications WITH (UPDLOCK, HOLDLOCK)
		WHERE LOWER(@ApplicationName) = LoweredApplicationName

		IF(@ApplicationId IS NULL)
		BEGIN
			SELECT  @ApplicationId = NEWID()
			INSERT  aspnet.Applications (ApplicationId, ApplicationName, LoweredApplicationName)
			VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
		END


		IF( @TranStarted = 1 )
		BEGIN
			IF(@@ERROR = 0)
			BEGIN
			SET @TranStarted = 0
			COMMIT TRANSACTION
			END
			ELSE
			BEGIN
				SET @TranStarted = 0
				ROLLBACK TRANSACTION
			END
		END
	END
END
GO

/*************************************************************/
/*************************************************************/
-- UnRegisterSchemaVersion SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'UnRegisterSchemaVersion')))
DROP PROCEDURE [aspnet].UnRegisterSchemaVersion
GO

CREATE PROCEDURE [aspnet].UnRegisterSchemaVersion
	@Feature                   nvarchar(128),
	@CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
	DELETE FROM aspnet.SchemaVersions
		WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END
GO

/*************************************************************/
/*************************************************************/
-- CreateUser SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'Users_CreateUser')))
DROP PROCEDURE [aspnet].Users_CreateUser
GO

CREATE PROCEDURE [aspnet].Users_CreateUser
	@ApplicationId    uniqueidentifier,
	@UserName         nvarchar(256),
	@IsUserAnonymous  bit,
	@LastActivityDate DATETIME,
	@UserId           uniqueidentifier OUTPUT
AS
BEGIN
	IF( @UserId IS NULL )
		SELECT @UserId = NEWID()
	ELSE
	BEGIN
		IF( EXISTS( SELECT UserId FROM aspnet.Users
					WHERE @UserId = UserId ) )
			RETURN -1
	END

	INSERT aspnet.Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
	VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)

	RETURN 0
END
GO

/*************************************************************/
/*************************************************************/
--- DeleteUser SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'Users_DeleteUser')))
DROP PROCEDURE [aspnet].Users_DeleteUser
GO

CREATE PROCEDURE [aspnet].Users_DeleteUser
	@ApplicationName  nvarchar(256),
	@UserName         nvarchar(256),
	@TablesToDeleteFrom int,
	@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
	DECLARE @UserId               uniqueidentifier
	SELECT  @UserId               = NULL
	SELECT  @NumTablesDeletedFrom = 0

	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END
	ELSE
	SET @TranStarted = 0

	DECLARE @ErrorCode   int
	DECLARE @RowCount    int

	SET @ErrorCode = 0
	SET @RowCount  = 0

	SELECT  @UserId = u.UserId
	FROM    aspnet.Users u, aspnet.Applications a
	WHERE   u.LoweredUserName       = LOWER(@UserName)
		AND u.ApplicationId         = a.ApplicationId
		AND LOWER(@ApplicationName) = a.LoweredApplicationName

	IF (@UserId IS NULL)
	BEGIN
		GOTO Cleanup
	END

	-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
	IF ((@TablesToDeleteFrom & 1) <> 0 AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'MembershipUsers'))))
	BEGIN
		DELETE FROM aspnet.Membership WHERE @UserId = UserId

		SELECT @ErrorCode = @@ERROR,
			   @RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount <> 0)
			SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
	END

	-- Delete from aspnet.UsersInRoles table if (@TablesToDeleteFrom & 2) is set
	IF ((@TablesToDeleteFrom & 2) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'UsersInRoles'))))
	BEGIN
		DELETE FROM aspnet.UsersInRoles WHERE @UserId = UserId

		SELECT @ErrorCode = @@ERROR,
				@RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount <> 0)
			SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
	END

	-- Delete from aspnet.Profile table if (@TablesToDeleteFrom & 4) is set
	IF ((@TablesToDeleteFrom & 4) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'Profiles'))) )
	BEGIN
		DELETE FROM aspnet.Profile WHERE @UserId = UserId

		SELECT @ErrorCode = @@ERROR,
				@RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount <> 0)
			SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
	END

	-- Delete from aspnet.PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
	IF ((@TablesToDeleteFrom & 8) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'WebPartState_User'))))
	BEGIN
		DELETE FROM aspnet.PersonalizationPerUser WHERE @UserId = UserId

		SELECT @ErrorCode = @@ERROR,
				@RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount <> 0)
			SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
	END

	-- Delete from aspnet.Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
	IF ((@TablesToDeleteFrom & 1) <> 0 AND
		(@TablesToDeleteFrom & 2) <> 0 AND
		(@TablesToDeleteFrom & 4) <> 0 AND
		(@TablesToDeleteFrom & 8) <> 0 AND
		(EXISTS (SELECT UserId FROM aspnet.Users WHERE @UserId = UserId)))
	BEGIN
		DELETE FROM aspnet.Users WHERE @UserId = UserId

		SELECT @ErrorCode = @@ERROR,
				@RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount <> 0)
			SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
	END

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		COMMIT TRANSACTION
	END

	RETURN 0

Cleanup:
	SET @NumTablesDeletedFrom = 0

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RETURN @ErrorCode

END
GO

/*************************************************************/
/*************************************************************/
--- aspnet.AnyDataInTables SP

IF (EXISTS (SELECT ROUTINE_NAME
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE (SPECIFIC_SCHEMA = N'aspnet' AND SPECIFIC_NAME = 'AnyDataInTables')))
DROP PROCEDURE [aspnet].AnyDataInTables
GO

CREATE PROCEDURE [aspnet].AnyDataInTables
	@TablesToCheck int
AS
BEGIN
	-- Check Membership table if (@TablesToCheck & 1) is set
	IF ((@TablesToCheck & 1) <> 0 AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'MembershipUsers'))))
	BEGIN
		IF (EXISTS(SELECT TOP 1 UserId FROM aspnet.Membership))
		BEGIN
			SELECT N'aspnet.Membership'
			RETURN
		END
	END

	-- Check aspnet.Roles table if (@TablesToCheck & 2) is set
	IF ((@TablesToCheck & 2) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'Roles'))) )
	BEGIN
		IF (EXISTS(SELECT TOP 1 RoleId FROM aspnet.Roles))
		BEGIN
			SELECT N'aspnet.Roles'
			RETURN
		END
	END

	-- Check aspnet.Profile table if (@TablesToCheck & 4) is set
	IF ((@TablesToCheck & 4) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'Profiles'))) )
	BEGIN
		IF (EXISTS(SELECT TOP 1 UserId FROM aspnet.[Profile]))
		BEGIN
			SELECT N'aspnet.Profile'
			RETURN
		END
	END

	-- Check aspnet.PersonalizationPerUser table if (@TablesToCheck & 8) is set
	IF ((@TablesToCheck & 8) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'PersonalizationPerUser'))) )
	BEGIN
		IF (EXISTS(SELECT TOP 1 UserId FROM aspnet.PersonalizationPerUser))
		BEGIN
			SELECT N'aspnet.PersonalizationPerUser'
			RETURN
		END
	END

	-- Check WebEvent_LogEvent table if (@TablesToCheck & 16) is set
	IF ((@TablesToCheck & 16) <> 0  AND
		(EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'WebEvent_LogEvent'))) )
	BEGIN
		IF (EXISTS(SELECT TOP 1 * FROM aspnet.WebEvent_Events))
		BEGIN
			SELECT N'aspnet.WebEvent_Events'
			RETURN
		END
	END

	-- Check aspnet.Users table if (@TablesToCheck & 1,2,4 & 8) are all set
	IF ((@TablesToCheck & 1) <> 0 AND
		(@TablesToCheck & 2) <> 0 AND
		(@TablesToCheck & 4) <> 0 AND
		(@TablesToCheck & 8) <> 0 AND
		(@TablesToCheck & 32) <> 0 AND
		(@TablesToCheck & 128) <> 0 AND
		(@TablesToCheck & 256) <> 0 AND
		(@TablesToCheck & 512) <> 0 AND
		(@TablesToCheck & 1024) <> 0)
	BEGIN
		IF (EXISTS(SELECT TOP 1 UserId FROM aspnet.Users))
		BEGIN
			SELECT N'aspnet.Users'
			RETURN
		END
		IF (EXISTS(SELECT TOP 1 ApplicationId FROM aspnet.Applications))
		BEGIN
			SELECT N'aspnet.Applications'
			RETURN
		END
	END
END
GO

DECLARE @command nvarchar(400)
SET @command = 'GRANT EXECUTE ON aspnet.AnyDataInTables TO ' + QUOTENAME(user)
EXEC (@command)
GO

/*************************************************************/
/*************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.VIEWS
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'ApplicationsView')))
BEGIN
  PRINT 'Creating the aspnet.ApplicationsView view...'
  EXEC('
  CREATE VIEW [aspnet].[ApplicationsView]
  AS SELECT [ApplicationName], [LoweredApplicationName], [ApplicationId], [Description]
  FROM [aspnet].[Applications]
  ')
END

/*************************************************************/
/*************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.VIEWS
                WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = 'UsersView')))
BEGIN
  PRINT 'Creating the aspnet.UsersView view...'
  EXEC('
  CREATE VIEW [aspnet].[UsersView]
  AS SELECT [ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]
  FROM [aspnet].[Users]
  ')
END

/*************************************************************/
/*************************************************************/
DECLARE @command nvarchar(4000)

SET @command = 'REVOKE EXECUTE ON [aspnet].RegisterSchemaVersion from ' + QUOTENAME(user)
EXEC (@command)
SET @command = 'REVOKE EXECUTE ON [aspnet].RegisterSchemaVersion from ' + QUOTENAME(user)
EXEC (@command)
GO

PRINT '----------------------------------------'
PRINT 'Completed execution of InstallCommon.SQL'
PRINT '----------------------------------------'
